/*
 * ============================================================================
 * Copyright Notice!
 * ----------------------------------------------------------------------------
 * This file contains proprietary and confidential information belonging to the
 * group of people involvled in developing this project.  Reproduction, disclosure 
 * or use without specific written authorization from project stake holders is 
 * prohibited. Please refer the readme.txt in project installation folder for more
 * specific contacts
 *
 * All rights reserved.
 * ============================================================================
 */
package edu.uwm.universitydb.dao.project;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import edu.uwm.universitydb.framework.nontransactional.db.command.impl.NonTransactionalDBFetchCommandBase;
import edu.uwm.universitydb.model.Department;
import edu.uwm.universitydb.model.Professor;
import edu.uwm.universitydb.model.Project;

/**
 * <code>SearchProjectCommandBase</type>
 * 
 * Base command class for project fetch from DB
 * 
 * @author Shyam Nair
 */
public abstract class SearchProjectCommandBase<T> extends NonTransactionalDBFetchCommandBase<T>
{

    /** attribute holding the tuple name for pno */
    private static final String TUPLE_COLUMN_PNO = "p.pno";

    /** attribute holding the tuple name for start date */
    private static final String TUPLE_COLUMN_START_DATE = "p.starting_date";

    /** attribute holding the tuple name for end date */
    private static final String TUPLE_COLUMN_END_DATE = "p.ending_date";

    /** attribute holding the tuple name for budget */
    private static final String TUPLE_COLUMN_BUDGET = "p.budget";

    /** attribute holding the tuple name for sponsor */
    private static final String TUPLE_COLUMN_SPONSOR = "p.sponser";

    /** attribute holding the tuple name for sponsor */
    private static final String TUPLE_COLUMN_PROF_SSN = "prof.prof_ssn";

    /** attribute holding the tuple name for prof age */
    private static final String TUPLE_COLUMN_PROF_AGE = "prof.age";

    /** attribute holding the tuple name for prof rank */
    private static final String TUPLE_COLUMN_PROF_RANK = "prof.rank";

    /** attribute holding the tuple name for prof name */
    private static final String TUPLE_COLUMN_PROF_NAME = "prof.name";
    /** attribute holding the tuple name for research speciality */
    private static final String TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY = "prof.research_speciality";

    /** attribute holding the tuple name for dept num */
    private static final String TUPLE_COLUMN_DEPT_NUM = "dept.dno";
    /** attribute holding the tuple name for dept name */
    private static final String TUPLE_COLUMN_DEPT_NAME = "dept.name";

    /** static string holding the select portion of query */
    protected static final String SELECT_SQL =
        "SELECT "
            + TUPLE_COLUMN_PNO
            + ", "
            + TUPLE_COLUMN_START_DATE
            + ", "
            + TUPLE_COLUMN_END_DATE
            + ","
            + TUPLE_COLUMN_BUDGET
            + ", "
            + TUPLE_COLUMN_SPONSOR
            + ", "
            + TUPLE_COLUMN_PROF_SSN
            + ","
            + TUPLE_COLUMN_PROF_AGE
            + ", "
            + TUPLE_COLUMN_PROF_RANK
            + ", "
            + TUPLE_COLUMN_PROF_NAME
            + ", "
            + TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY
            + ", "
            + TUPLE_COLUMN_DEPT_NUM
            + ", "
            + TUPLE_COLUMN_DEPT_NAME
            + " from university.project p, university.professor prof, university.manage, university.works, university.department dept";

    /** static string holding the order by budget portion of query */
    protected static final String SQL_ORDER_BY_BUDGET = " ORDER BY p.budget;";

    /** static string holding the order by pno portion of query */
    protected static final String SQL_ORDER_BY_PROJNUM = " ORDER BY p.pno;";

    /**
     * <code>getProject(...)</code>
     * 
     * @param result
     * @return
     * @throws SQLException
     */
    protected Project getProject(final ResultSet result) throws SQLException
    {
        final Integer projNum = result.getInt(TUPLE_COLUMN_PNO);
        final String sponsor = result.getString(TUPLE_COLUMN_SPONSOR);
        final Date startDate = result.getDate(TUPLE_COLUMN_START_DATE);
        final Long startDateVal = startDate.getTime();
        final Date endDate = result.getDate(TUPLE_COLUMN_END_DATE);
        final Long endDateVal = endDate.getTime();
        final Integer budget = result.getInt(TUPLE_COLUMN_BUDGET);
        final Integer deptId = result.getInt(TUPLE_COLUMN_DEPT_NUM);
        final String deptName = result.getString(TUPLE_COLUMN_DEPT_NAME);
        final Department dept = new Department(deptId, deptName);
        final String ssnNum = result.getString(TUPLE_COLUMN_PROF_SSN);
        final int age = result.getInt(TUPLE_COLUMN_PROF_AGE);
        final String name = result.getString(TUPLE_COLUMN_PROF_NAME);
        final String rank = result.getString(TUPLE_COLUMN_PROF_RANK);
        final String researchSpeciality = result.getString(TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY);
        final Professor professor =
            new Professor(ssnNum, age, name, rank, researchSpeciality, dept);
        return new Project(projNum, sponsor, new java.util.Date(startDateVal), new java.util.Date(
            endDateVal), budget, professor);
    }

}
/*
 * ============================================================================
 * Change History
 * ----------------------------------------------------------------------------
 * Apr 19, 2014   Shyam Nair
 * File created
 * ----------------------------------------------------------------------------
 */